﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using DreamBuilder.UI.UserControls.DataAccess.Services;
using System.Data;
using DreamBuilder.DictionaryAccess;
using DreamBuilder.Core.DAO;


namespace DreamBuilder.UI.UserControls.DataAccess.Components
{
    public class OracleUCChildDAL : UCChildDAL
    {
        public override  string UCChildDBHelperString { get; set; }
        public override DataTable GetChildDataTable(string childEditID, string parentValue)
        {
            DataTable childData = GetChildData(childEditID, parentValue);
            SetPKFieldToDataTable(childEditID, childData);
            return childData;
        }
        private void SetPKFieldToDataTable(string childEditID,DataTable childDataTable)
        {
            string pkField = UChildCommon.GetChildTablePKField(childEditID);
            childDataTable.PrimaryKey = new DataColumn[] { childDataTable.Columns[pkField] };
        }

        private DataTable GetChildData(string childEditID, string parentValue)
        {
            System.Collections.Generic.List<ChildTableItemInfo> childitemList = DictionaryAccessFacade.GetSortedChildTableItemsInfo(childEditID);
            ChildEditTotalInfo pChildEditTotalInfo = DictionaryAccessFacade.GetChildEditTotalInfo(childEditID);
            string childTableCode = pChildEditTotalInfo.TableCode;

            string sql = "select ";
            string selectSql = "";
            string fromSql = " from " + childTableCode;
            string tablefieldCode = "";
            int columnIndex = 0;
            Dictionary<string ,string> tempDictionary = new Dictionary<string,string>();
            childitemList.ForEach(
                delegate(ChildTableItemInfo childTableItemInfo)
                {
                    tablefieldCode = childTableCode + "." + childTableItemInfo.FieldCode;
                    selectSql += tablefieldCode + ",";

                    if (childTableItemInfo.ItemType == EDITITEMTYPE.TYPE_ComboBoxBool
                        || childTableItemInfo.ItemType == EDITITEMTYPE.TYPE_RadioBool)
                    {
                        //selectSql += "(case " + tablefieldCode + " when 1 then 'Yes' else 'No' end) as " + childTableCode + "__" + childTableItemInfo.FieldCode + ",";
                        selectSql += "(case " + tablefieldCode + " when 1 then 'Yes' else 'No' end) as COLUMN" + columnIndex + ",";
                        tempDictionary.Add("COLUMN" + columnIndex.ToString(), childTableCode + "__" + childTableItemInfo.FieldCode);
                        
                    }
                    else if (childTableItemInfo.ItemType == EDITITEMTYPE.TYPE_Reference
                    || childTableItemInfo.ItemType == EDITITEMTYPE.TYPE_ComboBoxReference)
                    {

                        FieldDicInfo pFieldDicInfo = DictionaryAccessFacade.GetFieldDicInfo(childTableCode, childTableItemInfo.FieldCode);
                        UCControlCommon.GetRefSelectAndFromSql(pFieldDicInfo, ref fromSql, ref selectSql, columnIndex);
                        tempDictionary.Add("COLUMN" + columnIndex.ToString(), pFieldDicInfo.TableCode + "__" + pFieldDicInfo.FieldCode);
                    }
                    else
                    {
                        //selectSql += tablefieldCode + " as \"" + childTableCode + "__" + childTableItemInfo.FieldCode + "\",";
                        selectSql += tablefieldCode + " as COLUMN" + columnIndex + ",";
                        tempDictionary.Add("COLUMN" + columnIndex.ToString(), childTableCode + "__" + childTableItemInfo.FieldCode);
                    }
                    columnIndex++;
                }
                );

            selectSql += "'2' as RowStatus" + ",'Ordinary' as RowAttribute";
            string whereSql = "";
            whereSql += " where ( " + childTableCode + "." + UChildCommon.GetChildTableFKField(childEditID) + " = '" + parentValue + "' )";
           
            if (!string.IsNullOrEmpty(pChildEditTotalInfo.RefRangeSQL))
            {
                whereSql += " and ( " + pChildEditTotalInfo.RefRangeSQL + " ) ";
            }

            string orderSql = string.IsNullOrEmpty(pChildEditTotalInfo.OrderSql)?"":" Order By " + childTableCode + "." + pChildEditTotalInfo.OrderSql.Replace(",", "," + childTableCode + ".");
            sql += selectSql + fromSql + whereSql + orderSql;
            DataTable dtInfo = new DataTable();
            if (!string.IsNullOrEmpty(UCChildDBHelperString))
            {
                IDBHelper dbHelper = DBHelperFactory.CreateInstance(UCChildDBHelperString);
                dtInfo = dbHelper.ExecuteDataSet(sql).Tables[0];
            }
            else
            {
                dtInfo = DBHelper.ExecuteDataSet(sql).Tables[0];
            }
            SetPKFieldToDataTable(childEditID, dtInfo);

            foreach (string key in tempDictionary.Keys)
            {
                string aliasName = "";
                tempDictionary.TryGetValue(key, out aliasName);
                dtInfo.Columns[key].ColumnName = aliasName;
            }
            return dtInfo;
        }
    }
}
